﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DALBase;
using OneMile.Entity.Model;
using System.Collections;
using System.Data;
using OneMile.Common;
using OneMile.Entity;

namespace OneMile.DAL
{
    public class DALUOrder:BaseDAL
    {
        public DBAccess db = DBConnection.CreateDB();

        public IList<EOrderForList> GetOrderListByUserId(string UserId, int PageSize, int PageNo, string shopsType, int OrderType)
        {
            try
            {
              StringBuilder sb = new StringBuilder();
              sb.AppendLine("SELECT Commodities,ShopsName,ShopsType,TotalPrice,OrderAddress,OrderId,OrderType,Rec_CreateTime,Rec_ModifyTime,RowNum");
              sb.AppendLine("  FROM (    SELECT (stuff((SELECT ' ' + bb.CommodityName +'x'+CONVERT(varchar(10), bb.Amount) ");
              sb.AppendLine("                             FROM Fct_Order aa");
              sb.AppendLine("                       INNER JOIN Rel_OrderDetails bb ON aa.OrderId=bb.OrderId  ");
              sb.AppendLine("                            WHERE aa.OrderId = a.OrderId for xml path('')),1,1,'') ) AS Commodities,");
              sb.AppendLine("                   b.ShopsName, b.ShopsType, a.TotalPrice, a.OrderAddress, a.OrderId,a.OrderType,a.Rec_CreateTime,a.Rec_ModifyTime,");
              sb.AppendLine("                   ROW_NUMBER() OVER(ORDER BY a.OrderNumber DESC) as RowNum");
              sb.AppendLine("              FROM Fct_Order a ");
              sb.AppendLine("        INNER JOIN Fct_Shops b ON a.ShopId=b.ShopsId");
              sb.AppendLine("        INNER JOIN Rel_OrderDetails c ON a.OrderId=c.OrderId");
              sb.AppendLine("             WHERE a.Disabled=0 AND a.UserId=@UserId");
              if (OrderType != 0)
              {
                  sb.AppendLine("                   AND a.OrderType=@OrderType");
              }
              if (!String.IsNullOrEmpty(shopsType))
              {
                  sb.AppendLine("               AND b.ShopsType LIKE @ShopType ");
              }
              sb.AppendLine("          GROUP BY a.OrderId,b.ShopsName,b.ShopsType,a.TotalPrice,a.OrderAddress,a.Rec_CreateTime,a.Rec_ModifyTime,a.OrderType,a.OrderNumber)AS D");
              sb.AppendLine(" WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize ");

              Hashtable hs = new Hashtable();
              hs.Add("UserId", UserId);
              hs.Add("PageNo", PageNo);
              hs.Add("PageSize", PageSize);
              if (OrderType != 0)
              {
                  hs.Add("OrderType", OrderType);
              }
              if (!String.IsNullOrEmpty(shopsType))
              {
                  hs.Add("ShopType", String.Format("%{0}%", shopsType));
              }

              DataTable dt = db.ExecuteQuery(sb.ToString(), hs);
              if (dt != null && dt.Rows.Count > 0)
              {
                  IList<EOrderForList> list = new List<EOrderForList>();
                  EOrderForList es = null;
                  foreach (DataRow dr in dt.Rows)
                  {
                      es = new EOrderForList();
                      es.OrderId = new Guid(dr["OrderId"].TString());
                      es.Commodities = dr["Commodities"].TString();
                      es.OrderAddress = dr["OrderAddress"].TString();
                      es.Rec_CreateTime = dr["Rec_CreateTime"].TDateTime();
                      es.Rec_ModifyTime = dr["Rec_ModifyTime"].TDateTime();
                      es.OrderType = dr["OrderType"].TInt32();
                      es.ShopsName = dr["ShopsName"].TString();
                      es.ShopsType = dr["ShopsType"].TString();
                      es.TotalPrice = dr["TotalPrice"].TDecimal();
                      list.Add(es);
                  }
                  return list;
              }
              return null;   
            }
            catch (Exception e)
            {
                base.WriteLog(e.Message);
                throw e;
            }
        }

        public EOrder GetOrderByOrderId(Guid orderId)
        {
            try
            {
                string sql = @"    
    SELECT [UserId],[ShopId],[OrderAddress],[OrderMobile],[OrderName],[OrderRemarks],
           A.[Rec_CreateBy],A.[TotalPrice],[EarliestTime],[LatestTime],[Freight],[CommodityId],
           [Amount],[CommodityPriceId],B.[UnitPrice],B.[CommodityAccessoriesName] AS CommodityAccessoriesNameList,[AttributeDetailsName] AS AttributeDetailsNameList,
           [CommodityName],B.[TotalPrice] AS DetailsTotalPrice,[ActualTotalPrice] AS DetailsActualPrice,B.[OrderDetailsId],
           C.[CommodityAccessoriesId],C.[CommodityAccessoriesName],[AttributeDetailsId]
      FROM Fct_Order A
INNER JOIN Rel_OrderDetails B ON B.OrderId=A.OrderId
 LEFT JOIN Rel_OrderDetailsAccessories C ON C.OrderDetailsId=B.OrderDetailsId
 LEFT JOIN Rel_OrderDetailsAttribute D ON D.OrderDetailsId=B.OrderDetailsId
     WHERE A.OrderId=@OrderId AND A.Disabled=0";

                Hashtable hs = new Hashtable();
                hs.Add("OrderId", orderId);

                DataTable dt = db.ExecuteQuery(sql, hs);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;
                }
                EOrder order = new EOrder();
                order.OrderId = Guid.NewGuid();
                order.UserId = dt.Rows[0]["UserId"].TGuid();
                order.ShopId = dt.Rows[0]["ShopId"].TGuid();
                order.OrderAddress = dt.Rows[0]["OrderAddress"].TString();
                order.EarliestTime = dt.Rows[0]["EarliestTime"].TDateTime();
                order.LatestTime = dt.Rows[0]["LatestTime"].TDateTime();
                order.Freight = dt.Rows[0]["Freight"].TDecimal();
                order.OrderMobile = dt.Rows[0]["OrderMobile"].TString();
                order.OrderName = dt.Rows[0]["OrderName"].TString();
                order.OrderRemarks = dt.Rows[0]["OrderRemarks"].TString();
                order.Rec_CreateBy = dt.Rows[0]["Rec_CreateBy"].TString();
                order.OrderType = 1;
                decimal orderTotalPrice = 0;
                List<DataRow> templist = new List<DataRow>();
                templist.Add(dt.Rows[0]);
                foreach (DataRow dr in dt.Rows)
                {
                    if (templist.Find(p => p["CommodityId"].TString().Equals(dr["CommodityId"].TString())) == null)
                    {
                        templist.Add(dr);
                    }
                }
                List<EOrderDetails> listDetails = new List<EOrderDetails>();
                foreach (DataRow dr in templist)
                {
                    EOrderDetails details = new EOrderDetails();
                    details.OrderId = order.OrderId;
                    details.OrderDetailsId = Guid.NewGuid();
                    details.ActualTotalPrice = dr["DetailsTotalPrice"].TDecimal();//实际价格按原价算
                    details.TotalPrice = dr["DetailsTotalPrice"].TDecimal();
                    details.UnitPrice = dr["UnitPrice"].TDecimal();
                    details.CommodityAccessoriesName = dr["CommodityAccessoriesNameList"].TString();
                    details.AttributeDetailsName = dr["AttributeDetailsNameList"].TString();
                    details.CommodityId = dr["CommodityId"].TGuid();
                    details.Amount = dr["Amount"].TInt32();
                    details.CommodityPriceId = dr["CommodityPriceId"].TGuid();
                    details.CommodityName = dr["CommodityName"].TString();
                    orderTotalPrice += details.TotalPrice;


                    DataRow[] drs = dt.Select(String.Format("CommodityId='{0}'", dr["CommodityId"].TString()));

                    List<EOrderDetailsAccessories> listAccess = new List<EOrderDetailsAccessories>();
                    EOrderDetailsAccessories eoda = new EOrderDetailsAccessories();
                    eoda.OrderDetailsId = details.OrderDetailsId;
                    eoda.OrderDetailsAccessoriesId = Guid.NewGuid();
                    eoda.CommodityAccessoriesId = drs[0]["CommodityAccessoriesId"].TGuid();
                    listAccess.Add(eoda);

                    List<EOrderDetailsAttribute> listAttr = new List<EOrderDetailsAttribute>();
                    EOrderDetailsAttribute eoda2 = new EOrderDetailsAttribute();
                    eoda2.OrderDetailsId = details.OrderDetailsId;
                    eoda2.OrderDetailsAttributeId = Guid.NewGuid();
                    eoda2.AttributeDetailsId = drs[0]["AttributeDetailsId"].TGuid();
                    listAttr.Add(eoda2);

                    foreach (DataRow dr1 in drs)
                    {
                        if (listAttr.Find(p => p.AttributeDetailsId.TString().Equals(dr1["AttributeDetailsId"].TString())) == null)
                        {
                            EOrderDetailsAttribute eodaa1 = new EOrderDetailsAttribute();
                            eodaa1.OrderDetailsId = details.OrderDetailsId;
                            eodaa1.OrderDetailsAttributeId = Guid.NewGuid();
                            eodaa1.AttributeDetailsId = dr1["AttributeDetailsId"].TGuid();
                            listAttr.Add(eodaa1);
                        }
                        if (listAccess.Find(p => p.CommodityAccessoriesId.TString().Equals(dr1["CommodityAccessoriesId"].TString())) == null)
                        {
                            EOrderDetailsAccessories eodaa2 = new EOrderDetailsAccessories();
                            eodaa2.OrderDetailsId = details.OrderDetailsId;
                            eodaa2.OrderDetailsAccessoriesId = Guid.NewGuid();
                            eodaa2.CommodityAccessoriesId = dr1["CommodityAccessoriesId"].TGuid();
                            listAccess.Add(eodaa2);
                        }
                    }

                    details.OrderDetailsAttributeList = listAttr;
                    details.OrderDetailsAccessoriesList = listAccess;
                    listDetails.Add(details);
                }
                order.TotalPrice = orderTotalPrice;
                order.OrderDetailsList = listDetails;

                return order;
            }
            catch (Exception e)
            {

                base.WriteLog(e.Message);
                throw e;
            }
            return null;
        }



        public IList<EOrderDetails> GetOrderDetails(string orderId)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("");
                sb.AppendLine("");
                sb.AppendLine("");
                sb.AppendLine("");
                sb.AppendLine("");
                sb.AppendLine("");
                sb.AppendLine("");
                sb.AppendLine("");
            }
            catch (Exception e)
            {

                base.WriteLog(e.Message);
                throw e;
            }
            return null;
        }

        public bool InsertOrder(EOrder order)
        {
            try
            {
                StringBuilder sbOrder = new StringBuilder();
                sbOrder.AppendLine("INSERT INTO Fct_Order ");
                sbOrder.AppendLine("([OrderId],[UserId],[OrderAddress],[OrderMobile],[OrderRemarks],[Freight],");
                sbOrder.AppendLine("[TotalPrice],[OrderName],[PromotionsId],[EarliestTime],[LatestTime],[ShopId],");
                sbOrder.AppendLine("[OrderType],[Rec_CreateTime],[Rec_CreateBy],[Rec_ModifyTime],[Rec_ModifyBy],[Disabled])");
                sbOrder.AppendLine("    VALUES");
                sbOrder.AppendLine("         (");
                sbOrder.AppendLine("           @OrderId,");
                sbOrder.AppendLine("           @UserId,");
                sbOrder.AppendLine("           @OrderAddress,");
                sbOrder.AppendLine("           @OrderMobile,");
                sbOrder.AppendLine("           @OrderRemarks,");
                sbOrder.AppendLine("           @Freight,");
                sbOrder.AppendLine("           @TotalPrice,");
                sbOrder.AppendLine("           @OrderName,");
                sbOrder.AppendLine("           @PromotionsId,");
                sbOrder.AppendLine("           @EarliestTime,");
                sbOrder.AppendLine("           @LatestTime,");
                sbOrder.AppendLine("           @ShopId,");
                sbOrder.AppendLine("           @OrderType,");
                sbOrder.AppendLine("           @Rec_CreateTime,");
                sbOrder.AppendLine("           @Rec_CreateBy,");
                sbOrder.AppendLine("           @Rec_ModifyTime,");
                sbOrder.AppendLine("           @Rec_ModifyBy,");
                sbOrder.AppendLine("           @Disabled");
                sbOrder.AppendLine("         )");
                Hashtable hsOrder = new Hashtable();
                hsOrder.Add("OrderId", order.OrderId);
                hsOrder.Add("UserId", order.UserId);
                hsOrder.Add("OrderAddress", order.OrderAddress);
                hsOrder.Add("OrderMobile", order.OrderMobile);
                hsOrder.Add("OrderRemarks", order.OrderRemarks);
                hsOrder.Add("Freight", order.Freight);
                hsOrder.Add("TotalPrice", order.TotalPrice);
                hsOrder.Add("OrderName", order.OrderName);
                hsOrder.Add("PromotionsId", order.PromotionsId);
                hsOrder.Add("EarliestTime", order.EarliestTime);
                hsOrder.Add("LatestTime", order.LatestTime);
                hsOrder.Add("ShopId", order.ShopId);
                hsOrder.Add("OrderType", order.OrderType);
                hsOrder.Add("Rec_CreateTime", DateTime.Now);
                hsOrder.Add("Rec_CreateBy", order.Rec_CreateBy);
                hsOrder.Add("Rec_ModifyTime", DateTime.Now);
                hsOrder.Add("Rec_ModifyBy", order.Rec_ModifyBy);
                hsOrder.Add("Disabled", order.Disabled);

                db.BeginTran();
                int num = Convert.ToInt32(db.ExecuteNonQuery(sbOrder.ToString(), hsOrder));
                if (num != 1)
                {
                    db.RollbackTran();
                    return false;
                }
                foreach (EOrderDetails od in order.OrderDetailsList)
                {
                    StringBuilder sbOD = new StringBuilder();
                    sbOD.AppendLine("INSERT INTO Rel_OrderDetails ");
                    sbOD.AppendLine("([OrderDetailsId],[OrderId],[CommodityId],[Amount],[CommodityPriceId],[CommodityName],[UnitPrice],");
                    sbOD.AppendLine("[TotalPrice],[Discount],[DiscountPrice],[PromotionsId],[ActualTotalPrice],[CommodityAccessoriesName],");
                    sbOD.AppendLine("[AttributeDetailsName],[Rec_CreateTime],[Rec_CreateBy],[Rec_ModifyTime] ,[Rec_ModifyBy],[Disabled])");
                    sbOD.AppendLine("    VALUES");
                    sbOD.AppendLine("         (");
                    sbOD.AppendLine("           @OrderDetailsId,");
                    sbOD.AppendLine("           @OrderId,");
                    sbOD.AppendLine("           @CommodityId,");
                    sbOD.AppendLine("           @Amount,");
                    sbOD.AppendLine("           @CommodityPriceId,");
                    sbOD.AppendLine("           @CommodityName,");
                    sbOD.AppendLine("           @UnitPrice,");
                    sbOD.AppendLine("           @TotalPrice,");
                    sbOD.AppendLine("           @Discount,");
                    sbOD.AppendLine("           @DiscountPrice,");
                    sbOD.AppendLine("           @PromotionsId,");
                    sbOD.AppendLine("           @ActualTotalPrice,");
                    sbOD.AppendLine("           @CommodityAccessoriesName,");
                    sbOD.AppendLine("           @AttributeDetailsName,");
                    sbOD.AppendLine("           @Rec_CreateTime,");
                    sbOD.AppendLine("           @Rec_CreateBy,");
                    sbOD.AppendLine("           @Rec_ModifyTime,");
                    sbOD.AppendLine("           @Rec_ModifyBy,");
                    sbOD.AppendLine("           @Disabled");
                    sbOD.AppendLine("         )");
                    Hashtable hsOD = new Hashtable();
                    hsOD.Add("OrderDetailsId", od.OrderDetailsId);
                    hsOD.Add("OrderId", od.OrderId);
                    hsOD.Add("CommodityId", od.CommodityId);
                    hsOD.Add("Amount", od.Amount);
                    hsOD.Add("CommodityPriceId", od.CommodityPriceId);
                    hsOD.Add("CommodityName", od.CommodityName);
                    hsOD.Add("UnitPrice", od.UnitPrice);
                    hsOD.Add("TotalPrice", od.TotalPrice);
                    hsOD.Add("Discount", od.Discount);
                    hsOD.Add("DiscountPrice", od.DiscountPrice);
                    hsOD.Add("PromotionsId", od.PromotionsId);
                    hsOD.Add("ActualTotalPrice", od.ActualTotalPrice);
                    hsOD.Add("CommodityAccessoriesName", od.CommodityAccessoriesName);
                    hsOD.Add("AttributeDetailsName", od.AttributeDetailsName);
                    hsOD.Add("Rec_CreateTime", DateTime.Now);
                    hsOD.Add("Rec_CreateBy", od.Rec_CreateBy);
                    hsOD.Add("Rec_ModifyTime", DateTime.Now);
                    hsOD.Add("Rec_ModifyBy", od.Rec_ModifyBy);
                    hsOD.Add("Disabled", od.Disabled);

                    int num2 = Convert.ToInt32(db.ExecuteNonQuery(sbOD.ToString(), hsOD));
                    if (num2 != 1)
                    {
                        db.RollbackTran();
                        return false;
                    }
                    foreach (EOrderDetailsAccessories odc in od.OrderDetailsAccessoriesList)
                    {
                        StringBuilder sbODC = new StringBuilder();
                        sbODC.AppendLine("INSERT INTO Rel_OrderDetailsAccessories ");
                        sbODC.AppendLine("([OrderDetailsAccessoriesId],[OrderDetailsId],[CommodityAccessoriesId],[Rec_CreateTime],[Rec_CreateBy],[Rec_ModifyTime],[Rec_ModifyBy],[Disabled])");
                        sbODC.AppendLine("    VALUES");
                        sbODC.AppendLine("         (");
                        sbODC.AppendLine("           @OrderDetailsAccessoriesId,");
                        sbODC.AppendLine("           @OrderDetailsId,");
                        sbODC.AppendLine("           @CommodityAccessoriesId,");
                        sbODC.AppendLine("           @Rec_CreateTime,");
                        sbODC.AppendLine("           @Rec_CreateBy,");
                        sbODC.AppendLine("           @Rec_ModifyTime,");
                        sbODC.AppendLine("           @Rec_ModifyBy,");
                        sbODC.AppendLine("           @Disabled");
                        sbODC.AppendLine("         )");
                        Hashtable hsODC = new Hashtable();
                        hsODC.Add("OrderDetailsAccessoriesId", odc.OrderDetailsAccessoriesId);
                        hsODC.Add("OrderDetailsId", odc.OrderDetailsId);
                        hsODC.Add("CommodityAccessoriesId", odc.CommodityAccessoriesId);
                        hsODC.Add("Rec_CreateTime", DateTime.Now);
                        hsODC.Add("Rec_CreateBy", odc.Rec_CreateBy);
                        hsODC.Add("Rec_ModifyTime", DateTime.Now);
                        hsODC.Add("Rec_ModifyBy", odc.Rec_ModifyBy);
                        hsODC.Add("Disabled", odc.Disabled);

                        int num3 = Convert.ToInt32(db.ExecuteNonQuery(sbODC.ToString(), hsODC));
                        if (num3 != 1)
                        {
                            db.RollbackTran();
                            return false;
                        }
                    }
                    foreach (EOrderDetailsAttribute oda in od.OrderDetailsAttributeList)
                    {
                        StringBuilder sbODA = new StringBuilder();
                        sbODA.AppendLine("INSERT INTO Rel_OrderDetailsAttribute ");
                        sbODA.AppendLine("([OrderDetailsAttributeId],[OrderDetailsId],[AttributeDetailsId],[Rec_CreateTime],[Rec_CreateBy],[Rec_ModifyTime],[Rec_ModifyBy],[Disabled])");
                        sbODA.AppendLine("    VALUES");
                        sbODA.AppendLine("         (");
                        sbODA.AppendLine("           @OrderDetailsAttributeId,");
                        sbODA.AppendLine("           @OrderDetailsId,");
                        sbODA.AppendLine("           @AttributeDetailsId,");
                        sbODA.AppendLine("           @Rec_CreateTime,");
                        sbODA.AppendLine("           @Rec_CreateBy,");
                        sbODA.AppendLine("           @Rec_ModifyTime,");
                        sbODA.AppendLine("           @Rec_ModifyBy,");
                        sbODA.AppendLine("           @Disabled");
                        sbODA.AppendLine("         )");
                        Hashtable hsODA = new Hashtable();
                        hsODA.Add("OrderDetailsAttributeId", oda.OrderDetailsAttributeId);
                        hsODA.Add("OrderDetailsId", oda.OrderDetailsId);
                        hsODA.Add("AttributeDetailsId", oda.AttributeDetailsId);
                        hsODA.Add("Rec_CreateTime", DateTime.Now);
                        hsODA.Add("Rec_CreateBy", oda.Rec_CreateBy);
                        hsODA.Add("Rec_ModifyTime", DateTime.Now);
                        hsODA.Add("Rec_ModifyBy", oda.Rec_ModifyBy);
                        hsODA.Add("Disabled", oda.Disabled);

                        int num4 = Convert.ToInt32(db.ExecuteNonQuery(sbODA.ToString(), hsODA));
                        if (num4 != 1)
                        {
                            db.RollbackTran();
                            return false;
                        }
                    }
                }

                if (order.CouponId != null && order.CouponId != Guid.Empty)
                {
                    string sql = @"UPDATE [Fct_Coupon] SET [IsUsed]=1,[UsedTime]=GETDATE(),[Rec_ModifyTime]=GETDATE()
                          WHERE [CouponId]=@CouponId";
                    Hashtable hsCoupon = new Hashtable();
                    hsCoupon.Add("CouponId", order.CouponId);
                    int num5 = Convert.ToInt32(db.ExecuteNonQuery(sql, hsCoupon));
                    if (num5 != 1)
                    {
                        db.RollbackTran();
                        return false;
                    }
                }
                db.CommitTran();
                return true;

            }
            catch (Exception ex)
            {
                db.RollbackTran();
                base.WriteLog("DALShopOrder_GetOrderDetailst" + ex.Message);
                return false;
            }
        }
    }
}
